package com.wy.util;

import com.wy.vo.CourseJs;
import com.wy.vo.Room;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * Created by wy on 2017/11/16.
 * 广西师范学院根据教师课表获得空闲教室
 */
public class SpareRoomImportFromCourse2 {
    //从教室课表获取信息
    private static String courseSql = "select a.jxlm,a.skzc,a.skxq,a.skjc,a.cxjc from t_coursebksxkjs a where a.zxjxjhh='2017-2018-1-2'";
    //用于插入空闲教室的sql
    private static String spareRoom_insert = "INSERT INTO `mobilecampus`.`t_room` ( `roomname`,`xn`, `xq`,`schoolid`,`usedate`,`divisionname`,`divisionid`,`class`)"
            + " VALUES" + "(" + "'{roomname}'," +  "'{xn}'," + "'{xq}'," +"'138', " + "'{usedate}'," + ");";
    //校历
    private static String xl = "SELECT * FROM t_schooltime WHERE xn='2017-2018' and xq=1 and type=1 and schoolid=178";
    private static String initWeek = "000000000000000000000000";
    private ResultSet rs;
    private PreparedStatement ps;
    //获取数据库连接
    public  Connection getConnection(){
        Connection conn = null;
        String driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://172.16.12.193:3306/mobilecampus";
        String user = "root";
        String pwd = "gxtc2016";
        try {
            Class.forName(driver);
            try {
                conn = DriverManager.getConnection(url, user, pwd);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //转换
    /**
     * 目前只能导  roomname教室名，xn学年，xq学期,class是否空闲，usedate使用日期
     *
     * 一条数据，根据skzc判断出第几周，然后xq判断日期，skjc和cxjc判断class
     *
     * if(skzc[i] == '1')
     *    {计算时间
     *    使用日期 = date.get(i+1).get(skxq);
     *    List classList = new List();
     *
     *    ;}
     *
     *
     *
     * */
    public void conversion(){
        Connection conn = this.getConnection();
        Map<String,ArrayList<CourseJs>> courseMap = getCourseMap(conn);
        char[] skzc = new char[25];//上课周次
        Map date = getSchoolTime(conn, xl);//获得日期,Map<String,List<Date>>
        Room room = null;
        //遍历courseMap
        for (Map.Entry<String,ArrayList<CourseJs>> entry : courseMap.entrySet()){
            room = new Room();
            room.setRoomname(entry.getKey());
            

        }

    }
    //获得课程
    private  ArrayList getCourseJs(Connection conn, String sql){
        ArrayList courseList = null;
        try {
            /**获得课程数据*/
            ps = conn.prepareStatement(courseSql);
            rs = ps.executeQuery();
            CourseJs courseJs;
            while(rs.next()){
                courseJs = new CourseJs();
                courseJs.setCxjc(rs.getString("cxjc"));
                courseJs.setJxlm(rs.getString("jxlm"));
                courseJs.setSkjc(rs.getString("skjc"));
                courseJs.setSkxq(rs.getString("skxq"));
                courseJs.setSkzc(rs.getString("skzc"));
                courseList.add(courseJs);
            }
            /**获得校历数据**/
            ps = conn.prepareStatement(xl);
            rs = ps.executeQuery();
//            while (rs.next()){
//
//            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return courseList;
    }
    //获取教室使用情况
    private Map<String,ArrayList<CourseJs>> getCourseMap(Connection conn){
        Map<String,ArrayList<CourseJs>> courseMap = new HashMap<String, ArrayList<CourseJs>>();
        try{
            ps = conn.prepareStatement(courseSql);
            rs = ps.executeQuery();
            CourseJs courseJs;
            String jxlm = null;
            ArrayList<CourseJs> courseJsArrayList;
            while(rs.next()){
                jxlm = rs.getString("jxlm");
                courseJs = new CourseJs();
                courseJs.setCxjc(rs.getString("cxjc"));
                courseJs.setJxlm(rs.getString("jxlm"));
                courseJs.setSkjc(rs.getString("skjc"));
                courseJs.setSkxq(rs.getString("skxq"));
                courseJs.setSkzc(rs.getString("skzc"));
                //判断是否该教学楼信息已经放入Map中
                if (courseMap.containsKey(jxlm)){
                    courseMap.get(jxlm).add(courseJs);
                }else{
                    courseJsArrayList = new ArrayList<CourseJs>();
                    courseJsArrayList.add(courseJs);
                    courseMap.put(jxlm,courseJsArrayList);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return courseMap;
    }
    //获得校历时间 Map,其中key为上课周次，value为该周次所对应的一周的日期
    private Map<String, List<Date>> getSchoolTime(Connection conn, String sql){
        Map<String, List<Date>> map = new HashMap<String, List<Date>>();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                int type = rs.getInt("type");
                int xlzc = rs.getInt("zc");
                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                String starttime = rs.getString("starttime");
                Date startDate= null;
                try {
                    startDate =  formatter.parse(starttime);
                } catch (ParseException e) {
                    e.printStackTrace();
                }
                if (type == 1) {
                    /**周次zc_1,第1周所对应的时间*/
                    String zc = "zc_" +xlzc;
                    List<Date> date = new ArrayList<Date>();
                    Calendar calendar = Calendar.getInstance();
                    // System.out.println(zc);
                    for (int i = 0; i < 7; i++) {
                        calendar.setTime(startDate);
                        calendar.add(Calendar.DAY_OF_MONTH, i);
                        date.add(calendar.getTime());
                        //System.out.println(calendar.getTime());
                    }
                    map.put(zc, date);
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  map;
    }


    public static void main(String[] args) {
        SpareRoomImportFromCourse2 s = new SpareRoomImportFromCourse2();
        Connection conn = s.getConnection();
        s.getSchoolTime(conn, xl);
    }
}
